#!/usr/bin/env python
# -*- coding: utf-8 -*-
from string import Template
import re
from tack.lib import json_app, string_app, content_headers, status_codes
from tack_ocie.api.util import build_row_dict, sql_string

## SQL statements below

select_all = """
SELECT * 
FROM ocie_report
WHERE active = 1
ORDER BY report_id
"""

select_one = """
SELECT * 
FROM ocie_report
WHERE report_id = $report_id AND active = 1
"""

update_to_inactive = """
UPDATE ocie_report
SET active = 0
WHERE report_id = $report_id
"""

update_one = """
UPDATE ocie_report
SET title = $title, description = $description, query = $query 
WHERE report_id = $report_id and active = 1
"""

insert_one = """
INSERT INTO ocie_report (title, description, query)
VALUES ($title, $description, $query )
"""

def get(environ, start_response):
    cursor = environ['connection'].cursor()
    cursor.execute(select_all)
    rows = cursor.fetchall()
    output = []
    for row in rows:
        row_dict = build_row_dict(cursor.description, row)
        output.append(row_dict)
    return json_app(output)(environ, start_response)
    
def delete(environ, start_response):
    report_id = environ['dispatcher.groups']['report_id']
    sql = Template(select_one).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        sql = Template(update_to_inactive).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        output = 'Deleted'
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return string_app(output)(environ, start_response)
    
def update(environ, start_response):
    report_id = environ['dispatcher.groups']['report_id']
    sql = Template(select_one).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        form = environ['form']
        title = sql_string(form['title'])
        description = sql_string(form['description'])
        query = sql_string(form['query'])
        if re.search('INSERT|SELECT INTO|DELETE|CREATE|DROP', query, re.I):
            start_response(status_codes.conflict, content_headers.default_plain())
            return ['409 Conflict']
        sql = Template(update_one).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        output = 'Updated'
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return string_app(output)(environ, start_response)

def add(environ, start_response):
    form = environ['form']
    title = sql_string(form['title'])
    description = sql_string(form['description'])
    query = sql_string(form['query'])
    if re.search('INSERT|SELECT INTO|DELETE|CREATE|DROP', query, re.I):
        start_response(status_codes.conflict, content_headers.default_plain())
        return ['409 Conflict']
    sql = Template(insert_one).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    environ['connection'].commit()
    output = 'Added'
    return string_app(output)(environ, start_response)
    
